CREATE VIEW [dbo].[vParticipantRoster]
AS
SELECT
    [dbo].[CustomerExperience].[CustomerExperienceKey],
    [dbo].[CustomerExperience].[UserKey],
    [dbo].[vOfferedCustomerExperience].[ProgramKey],
    [dbo].[vOfferedCustomerExperience].[ProgramName],
    [dbo].[vOfferedCustomerExperience].[ExperienceDefinitionKey],
    [dbo].[vOfferedCustomerExperience].[ExperienceDefinitionName],
    [dbo].[vOfferedCustomerExperience].[OfferingKey],
    [dbo].[vOfferedCustomerExperience].[OfferingName],
    [dbo].[vOfferedCustomerExperience].[ContactKey] AS Provider,
    [dbo].[vOfferedCustomerExperience].[OfferingName] AS Name,
    [dbo].[vBoNetContact].[LastName],
    [dbo].[vBoNetContact].[FirstName],
    [dbo].[vBoNetContact].[FullName],
    [dbo].[vBoNetContact].[SortName],
    [dbo].[vBoNetContact].[Company] AS CompanyName,
    [dbo].[vBoNetContact].[FullAddress],
    [dbo].[GradeRef].[GradeValue] AS Grade,
    [dbo].[CustomerExperience].[CustomerExperienceStatusCode] AS StatusCode,
    [dbo].[CustomerExperienceStatusRef].[CustomerExperienceStatusName] AS StatusName,
    [dbo].[vOfferedCustomerExperience].[LocationKey],
    [dbo].[vOfferedCustomerExperience].[LocationName],
    [dbo].[vOfferedCustomerExperience].[NumberOfUnits],
    [dbo].[vOfferedCustomerExperience].[OfferingDate] AS Date

FROM
    [dbo].[CustomerExperience]
        INNER JOIN [dbo].[vOfferedCustomerExperience] ON [dbo].[CustomerExperience].[CustomerExperienceKey] = [dbo].[vOfferedCustomerExperience].[OfferedCustomerExperienceKey]
        INNER JOIN [dbo].[vBoNetContact] ON [dbo].[CustomerExperience].[UserKey] = [dbo].[vBoNetContact].[ContactKey]
        INNER JOIN [dbo].[CustomerExperienceStatusRef] ON [dbo].[CustomerExperience].[CustomerExperienceStatusCode] = [dbo].[CustomerExperienceStatusRef].[CustomerExperienceStatusCode]
        LEFT JOIN [dbo].[GradeRef] ON [dbo].[vOfferedCustomerExperience].[OfferedCustomerExperienceGradeKey] = [dbo].[GradeRef].[GradeKey]

UNION ALL
SELECT
    [CustomerExperience1].[CustomerExperienceKey],
    [CustomerExperience1].[UserKey],
    [dbo].[vUnofferedCustomerExperience].[ProgramKey],
    [dbo].[vUnofferedCustomerExperience].[ProgramName],
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionKey],
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionName],
    NULL AS OfferingKey,
    NULL AS OfferingName,
    NULL AS Provider,
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionName] AS Name,
    [vBoNetContact1].[LastName],
    [vBoNetContact1].[FirstName],
    [vBoNetContact1].[FullName],
    [vBoNetContact1].[SortName],
    [vBoNetContact1].[Company] AS CompanyName,
    [vBoNetContact1].[FullAddress],
    [dbo].[vUnofferedCustomerExperience].[UnofferedCustomerExperienceGrade] AS Grade,
    [CustomerExperience1].[CustomerExperienceStatusCode] AS StatusCode,
    [CustomerExperienceStatusCodeRef1].[CustomerExperienceStatusName] AS StatusName,
    NULL AS LocationKey,
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionLocation] AS LocationName,
    [dbo].[vUnofferedCustomerExperience].[NumberOfUnits],
    [dbo].[vUnofferedCustomerExperience].[ExperienceDefinitionDate] AS Date

FROM
    [dbo].[CustomerExperience] AS [CustomerExperience1]
        INNER JOIN [dbo].[vUnofferedCustomerExperience] ON [CustomerExperience1].[CustomerExperienceKey] = [dbo].[vUnofferedCustomerExperience].[UnofferedCustomerExperienceKey]
        INNER JOIN [dbo].[vBoNetContact] AS [vBoNetContact1] ON [CustomerExperience1].[UserKey] = [vBoNetContact1].[ContactKey]
        INNER JOIN [dbo].[CustomerExperienceStatusRef] AS [CustomerExperienceStatusCodeRef1] ON [CustomerExperience1].[CustomerExperienceStatusCode] = [CustomerExperienceStatusCodeRef1].[CustomerExperienceStatusCode]

UNION ALL
SELECT
    [CustomerExperience2].[CustomerExperienceKey],
    [CustomerExperience2].[UserKey],
    [dbo].[vUserDefinedCustomerExperience].[ProgramKey],
    [dbo].[vUserDefinedCustomerExperience].[ProgramName],
    NULL AS ExperienceDefinitionKey,
    NULL AS ExperienceDefinitionName,
    NULL AS OfferingKey,
    NULL AS OfferingName,
    NULL AS Provider,
    [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceName] AS Name,
    [vBoNetContact2].[LastName],
    [vBoNetContact2].[FirstName],
    [vBoNetContact2].[FullName],
    [vBoNetContact2].[SortName],
    [vBoNetContact2].[Company] AS CompanyName,
    [vBoNetContact2].[FullAddress],
    NULL AS Grade,
    [CustomerExperience2].[CustomerExperienceStatusCode] AS StatusCode,
    [CustomerExperienceStatusCodeRef2].[CustomerExperienceStatusName] AS StatusName,
    NULL AS LocationKey,
    [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceLocation] AS LocationName,
    [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceUnits] AS NumberOfUnits,
    [dbo].[vUserDefinedCustomerExperience].[CompletionDate] AS Date

FROM
    [dbo].[CustomerExperience] AS [CustomerExperience2]
        INNER JOIN [dbo].[vUserDefinedCustomerExperience] ON [CustomerExperience2].[CustomerExperienceKey] = [dbo].[vUserDefinedCustomerExperience].[UserDefinedCustomerExperienceKey]
        INNER JOIN [dbo].[vBoNetContact] AS [vBoNetContact2] ON [CustomerExperience2].[UserKey] = [vBoNetContact2].[ContactKey]
        INNER JOIN [dbo].[CustomerExperienceStatusRef] AS [CustomerExperienceStatusCodeRef2] ON [CustomerExperience2].[CustomerExperienceStatusCode] = [CustomerExperienceStatusCodeRef2].[CustomerExperienceStatusCode]

GO